import pymysql
from todo import Todo
class TodoStoreWithMysql:
    @staticmethod
    def get_connection():
        return pymysql.Connection(host='192.168.224.134',database='todos',user='root',password='root@123ABC',autocommit=True)
    def init_db(self):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('''
            CREATE TABLE if not exists `todos`(
            `todo_id` int NOT NULL AUTO_INCREMENT,
            `title` varchar(255) NOT NULL DEFAULT '',
            `completed` int NOT NULL DEFAULT 0,
             PRIMARY KEY (`todo_id`))
            ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;''')
            

    def create(self,title:str):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("insert into todos(title) values(%s)",(title,))
            cursor.execute("select last_insert_id()")
            (todo_id,)=cursor.fetchone()

            return Todo(title,id=todo_id)
    def convert_row_to_obiect(self,row):
        return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def get_list(self):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('select * from todos')
            todos_list=cursor.fetchall()
            object_list=[]
            for row in todos_list:
                object_list.append(self.convert_row_to_obiect(row))
            return object_list

    def get_todo_byid(self,todo_id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute('select * from todos where todo_id=%s',(todo_id,))
            todo=cursor.fetchone()
            if todo is not None:
                return self.convert_row_to_obiect(todo)
            else:
                return None
    def mark_completed(self,todo_id):
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("update todos set completed=1 where todo_id=%s",(todo_id,))
            conn.commit()
            return self.get_todo_byid(todo_id)
    def del_a(self,todo_id):
        a=self.get_todo_byid(todo_id)
        with self.get_connection() as conn:
            cursor=conn.cursor()
            cursor.execute("delete from todos where todo_id=%s ",(todo_id,))
            conn.commit()
            return a

if __name__=='__main__':
    manger=TodoStoreWithMysql()
